﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace SQLDeployActions
{
    internal class DeployActionExecute
    {
        private DeployActionExecute()
        {
        }

        internal static void ExecuteSQLFile(string path, bool IsDML)
        {
            StringBuilder sqlcmdBuilder = new StringBuilder();
            StreamWriter ddlWriter;
            FileInfo sqlFile;

            sqlFile = new FileInfo(DeployConstants.BaseSource + path);
            if (sqlFile != null)
            {
                switch (IsDML)
                {
                    case false:
                        sqlcmdBuilder.Append(":r " + "\"" + sqlFile.FullName + "\"");
                        sqlcmdBuilder.Append(System.Environment.NewLine);
                        //sqlcmdBuilder.Remove(0, sqlcmdBuilder.Length - 1);                        
                        break;
                    case true:
                        sqlcmdBuilder.Append("BEGIN TRANSACTION" + System.Environment.NewLine);
                        sqlcmdBuilder.Append("BEGIN TRY" + System.Environment.NewLine);
                        sqlcmdBuilder.Append(":r " + "\"" + sqlFile.FullName + "\"");
                        sqlcmdBuilder.Append(System.Environment.NewLine);
                        sqlcmdBuilder.Append(";COMMIT TRANSACTION" + System.Environment.NewLine);
                        sqlcmdBuilder.Append("END TRY" + System.Environment.NewLine);
                        sqlcmdBuilder.Append("BEGIN CATCH" + System.Environment.NewLine);
                        sqlcmdBuilder.Append(@"SELECT  ERROR_MESSAGE() as SQLDeployErrorMessage;"
                            /*  ERROR_LINE() as ErrorLine,
                              ERROR_PROCEDURE() as ErrorProcedure;"*/
                                            + System.Environment.NewLine);
                        sqlcmdBuilder.Append("ROLLBACK TRANSACTION" + System.Environment.NewLine);
                        sqlcmdBuilder.Append("END CATCH" + System.Environment.NewLine);
                        sqlcmdBuilder.Append("GO" + System.Environment.NewLine);
                        //sqlcmdBuilder.Remove(0, sqlcmdBuilder.Length - 1);                        
                        break;
                }
                ddlWriter = new StreamWriter(IsDML ? DeployConstants.DMLFile : DeployConstants.DDLFile, false);
                ddlWriter.Write(sqlcmdBuilder.ToString());
                ddlWriter.Flush();
                ddlWriter.Close();
                ddlWriter.Dispose();
                RunSQLScript(IsDML ? DeployConstants.DMLFile : DeployConstants.DDLFile, sqlFile.FullName);
            }
        }

        internal static void RunSQLScript(string ScriptFile, string contentFile)
        {
            Console.WriteLine("Executing: " + contentFile);
            string msg = string.Empty;
            StringBuilder SQLCMD_Command = new StringBuilder();
            SQLCMD_Command.Append(" -m-1 -h-1 ");
            SQLCMD_Command.Append(DeployConstants.SERVER_OPTION + DeployConstants.ServerName);
            SQLCMD_Command.Append(DeployConstants.DB_OPTION + DeployConstants.DBName);
            SetSecurityOption(SQLCMD_Command);
            SQLCMD_Command.Append(DeployConstants.INPUT_FILE_OPTION + ScriptFile);
            msg = ExecuteProcess(DeployConstants.SQLCMD_EXE, SQLCMD_Command.ToString());
            Console.WriteLine(msg);
            msg = msg.ToLower();

            /* Get severity level of the error, if it is more than 10 then it is an error, else it is an info message
             * If it is an error, throw exception, else continue.
             * In case of DML, the actual error will be suppressed by the transaction rollback, in that case our select stmt
             * will help us to provide the error just before rollback.
             * Refer to CreateSQLRunBatch() Method
             */

            if ((msg.Contains("SQLDeployErrorMessage".ToLower())) || ((msg.Contains("msg") && IsSevereErrorMessage(msg) )))
            {
                throw new DeployException(DeployConstants.MSG_ERROR_DETECTED);
            }
        }

        private static bool IsSevereErrorMessage(string msg)
        {
            return Regex.IsMatch(msg, DeployConstants.REGEX_ERRORLEVELMATCH, RegexOptions.IgnoreCase);
        }

        internal static void SetSecurityOption(StringBuilder SQLCMD_Command)
        {
            if (DeployConstants.IsTrustedConnection)
                SQLCMD_Command.Append(DeployConstants.TRUSTED_CONNECTION_OPTION);
            else
            {
                SQLCMD_Command.Append(DeployConstants.USER_ID_OPTION + DeployConstants.UserName);
                SQLCMD_Command.Append(DeployConstants.PASSWORD_OPTION + DeployConstants.Password);
            }
        }

        internal static string ExecuteProcess(string command, string args)
        {
            StringBuilder outputBuilder = new StringBuilder();
            string output = string.Empty;
            string error = string.Empty;

            ProcessStartInfo ps = new ProcessStartInfo();
            ps.FileName = command;
            ps.Arguments = args;
            ps.RedirectStandardOutput = true;
            ps.RedirectStandardError = true;
            ps.UseShellExecute = false;
            ps.CreateNoWindow = true;
            Process winProc = new Process();
            winProc.StartInfo = ps;
            winProc.Start();

            output = winProc.StandardOutput.ReadToEnd();
            error = winProc.StandardError.ReadToEnd();
            winProc.Dispose();
            if (output.Length > 0)
            {
                outputBuilder.Append(DeployConstants.MSG_HEADING_STD_OUTPUT + System.Environment.NewLine);
                outputBuilder.Append(DeployConstants.MSG_UNDERLINE + System.Environment.NewLine);
                outputBuilder.Append(output + System.Environment.NewLine);
            }

            if (error.Length > 0)
            {
                outputBuilder.Append(DeployConstants.MSG_HEADING_STD_ERROR + System.Environment.NewLine);
                outputBuilder.Append(DeployConstants.MSG_UNDERLINE + System.Environment.NewLine);
                outputBuilder.Append(error + System.Environment.NewLine);
            }

            if (outputBuilder.Length == 0)
            {
                outputBuilder.Append(DeployConstants.MSG_EXECUTION_SUCCESS);
                outputBuilder.Append(System.Environment.NewLine);
            }

            return outputBuilder.ToString();
        }
    }
}
